﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using ESRI.ArcGIS.Geometry;
using System.Collections;
using GDCal.vo;
using System.Data.SqlClient;
using System.Data;

namespace GDCal.dao
{
    public class LandStreetPriceDetailDao : AbstractDao
    {
        public static ArrayList filter(LandStreetPriceDetailVO filterObj)
        {
            ArrayList ret = new ArrayList();
            SqlDataReader rs;
            LandStreetPriceDetailVO temp = null;
            SqlConnection oConn = getConnection();

            String whereClause = buildWhereClause(filterObj);
            String strSQL = "SELECT id, clp_id, land_identify, land_area, street_identify, street_name, max_price, min_price, street_layer_name, land_area_by_buffer, land_code_description FROM Land_Street_Price_Detail";
            if (whereClause != null)
                strSQL += whereClause;

            SqlCommand oComd = new SqlCommand(strSQL, oConn);
            oComd.CommandType = CommandType.Text;

            try
            {
                rs = oComd.ExecuteReader();
            }
            catch (Exception ex)
            {
                throw ex;
            }

            while (rs.Read())
            {
                temp = new LandStreetPriceDetailVO();
                temp.setId(rs.GetInt32(0));
                temp.setClpId(rs.GetInt32(1));
                temp.setLandIdentify(rs.GetString(2));
                temp.setLandArea(rs.GetDecimal(3));
                temp.setStreetIdentify(rs.GetString(4));
                temp.setStreetName(rs.GetString(5));
                temp.setMaxPrice(rs.GetDecimal(6));
                temp.setMinPrice(rs.GetDecimal(7));
                temp.setStreetLayerName(rs.GetString(8));
                temp.setLandAreaByBuffer(rs.GetString(9));
                temp.setLandCodeDescription(rs.GetString(10));
                ret.Add(temp);
            }

            oConn.Close();
            return ret;
        }

        private static String buildWhereClause(LandStreetPriceDetailVO filterObj)
        {
            String ret = "";
            Boolean addAnd = false;
            if (filterObj != null)
            {
                if (filterObj.getId() > 0)
                {
                    ret += " id = " + filterObj.getId();
                    addAnd = true;
                }

                if (filterObj.getClpId() > 0)
                {
                    if (addAnd)
                    {
                        ret += " AND";
                    }
                    addAnd = true;
                    ret += " clp_id = " + filterObj.getClpId();
                }

                if (filterObj.getLandIdentify() != null && !"".Equals(filterObj.getLandIdentify()))
                {
                    if (addAnd)
                    {
                        ret += " AND";
                    }
                    addAnd = true;
                    ret += " land_identify like (N'%" + filterObj.getLandIdentify() + "')";
                }

                if (filterObj.getLandArea() >= 0)
                {
                    if (addAnd)
                    {
                        ret += " AND";
                    }
                    addAnd = true;
                    ret += " land_area = " + filterObj.getLandArea();
                }

                if (filterObj.getStreetIdentify() != null && !"".Equals(filterObj.getStreetIdentify()))
                {
                    if (addAnd)
                    {
                        ret += " AND";
                    }
                    addAnd = true;
                    ret += " street_identify like (N'%" + filterObj.getStreetIdentify() + "%')";
                }

                if (filterObj.getStreetName() != null && !"".Equals(filterObj.getStreetName()))
                {
                    if (addAnd)
                    {
                        ret += " AND";
                    }
                    addAnd = true;
                    ret += " street_name like (N'%" + filterObj.getStreetName() + "%')";
                }

                if (filterObj.getMaxPrice() >= 0)
                {
                    if (addAnd)
                    {
                        ret += " AND";
                    }
                    addAnd = true;
                    ret += " max_price = " + filterObj.getMaxPrice();
                }

                if (filterObj.getMinPrice() >= 0)
                {
                    if (addAnd)
                    {
                        ret += " AND";
                    }
                    addAnd = true;
                    ret += " min_price = " + filterObj.getMinPrice();
                }

                if (filterObj.getStreetLayerName() != null && !"".Equals(filterObj.getStreetLayerName()))
                {
                    if (addAnd)
                    {
                        ret += " AND";
                    }
                    addAnd = true;
                    ret += " street_layer_name like (N'%" + filterObj.getStreetLayerName() + "%')";
                }

                if (filterObj.getLandAreaByBuffer() != null && !"".Equals(filterObj.getLandAreaByBuffer()))
                {
                    if (addAnd)
                    {
                        ret += " AND";
                    }
                    addAnd = true;
                    ret += " land_area_by_buffer like (N'%" + filterObj.getLandAreaByBuffer() + "%')";
                }

                if (filterObj.getLandCodeDescription() != null && !"".Equals(filterObj.getLandCodeDescription()))
                {
                    if (addAnd)
                    {
                        ret += " AND";
                    }
                    addAnd = true;
                    ret += " land_code_description like (N'%" + filterObj.getLandCodeDescription() + "%')";
                }

                if (!"".Equals(ret))
                {
                    ret = " WHERE" + ret;
                }
            }
            return ret;
        }

        public static int delAllLandStreetByClpId(Int32 clpId)
        {
            int ret;
            SqlConnection oConn = getConnection();
            String strSQL = "DELETE FROM Land_Street_Price_Detail WHERE clp_id = " + clpId;

            SqlCommand oComd = new SqlCommand(strSQL, oConn);
            oComd.CommandType = CommandType.Text;

            try
            {
                ret = oComd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                throw ex;
            }

            oConn.Close();
            return ret;
        }

        public static int delLandStreetPriceDetail(Int32 delId)
        {
            int ret;
            SqlConnection oConn = getConnection();
            String strSQL = "DELETE FROM Land_Street_Price_Detail WHERE id = " + delId;

            SqlCommand oComd = new SqlCommand(strSQL, oConn);
            oComd.CommandType = CommandType.Text;

            try
            {
                ret = oComd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                throw ex;
            }

            oConn.Close();
            return ret;
        }

        public static LandStreetPriceDetailVO getLandStreetPriceDetail(int id)
        {
            SqlDataReader rs;
            LandStreetPriceDetailVO ret = null;
            if (id == 0)
            {
                ret = new LandStreetPriceDetailVO();
                return ret;
            }
            SqlConnection oConn = getConnection();

            String strSQL = "SELECT clp_id, land_identify, land_area, street_identify, street_name, max_price, min_price, street_layer_name, land_area_by_buffer, land_code_description FROM Land_Street_Price_Detail WHERE id = " + id;
            SqlCommand oComd = new SqlCommand(strSQL, oConn);
            oComd.CommandType = CommandType.Text;

            try
            {
                rs = oComd.ExecuteReader();
            }
            catch (Exception ex)
            {
                throw ex;
            }

            if (rs.Read())
            {
                ret = new LandStreetPriceDetailVO();
                ret.setId(id);
                ret.setClpId(rs.GetInt32(0));
                ret.setLandIdentify(rs.GetString(1));
                ret.setLandArea(rs.GetDecimal(2));
                ret.setStreetIdentify(rs.GetString(3));
                ret.setStreetName(rs.GetString(4));
                ret.setMaxPrice(rs.GetDecimal(5));
                ret.setMinPrice(rs.GetDecimal(6));
                ret.setStreetLayerName(rs.GetString(7));
                ret.setLandAreaByBuffer(rs.GetString(8));
                ret.setLandCodeDescription(rs.GetString(9));
            }

            oConn.Close();
            return ret;
        }

        public static int insertLandStreetPriceDetail(LandStreetPriceDetailVO lspdVO)
        {
            int ret;
            SqlConnection oConn = getConnection();
            String strSQL = "INSERT INTO Land_Street_Price_Detail(clp_id, land_identify, land_area, street_identify, street_name, max_price, min_price, street_layer_name, land_area_by_buffer, land_code_description) VALUES ("
                            + lspdVO.getClpId() + ", "
                            + "(N'" + lspdVO.getLandIdentify() + "'), "
                            + lspdVO.getLandArea() + ", "
                            + "(N'" + lspdVO.getStreetIdentify() + "'), "
                            + "(N'" + lspdVO.getStreetName() + "'), "
                            + lspdVO.getMaxPrice() + ", "
                            + lspdVO.getMinPrice() + ", "
                            + "(N'" + lspdVO.getStreetLayerName() + "'), "
                            + "(N'" + lspdVO.getLandAreaByBuffer() + "'), "
                            + "(N'" + lspdVO.getLandCodeDescription() + "')) ";

            SqlCommand oComd = new SqlCommand(strSQL, oConn);
            oComd.CommandType = CommandType.Text;

            try
            {
                ret = oComd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                throw ex;
            }

            oConn.Close();
            return ret;
        }

        public static int updateLandStreetPriceDetail(LandStreetPriceDetailVO lspdVO)
        {
            int ret;
            SqlConnection oConn = getConnection();

            String strSQL = "UPDATE Land_Street_Price_Detail SET "
                            + "clp_id = " + lspdVO.getClpId() + ", "
                            + "land_identify=(N'" + lspdVO.getLandIdentify() + "'), "
                            + "land_area = " + lspdVO.getLandArea() + ", "
                            + "street_identify=(N'" + lspdVO.getStreetIdentify() + "'), "
                            + "street_name=(N'" + lspdVO.getStreetName() + "'), "
                            + "max_price = " + lspdVO.getMaxPrice() + ", "
                            + "min_price = " + lspdVO.getMinPrice() + ", "
                            + "street_layer_name=(N'" + lspdVO.getStreetLayerName() + "'), "
                            + "land_area_by_buffer=(N'" + lspdVO.getLandAreaByBuffer() + "'), "
                            + "land_code_description=(N'" + lspdVO.getLandCodeDescription() + "') "
                            + "WHERE id = " + lspdVO.getId();

            SqlCommand oComd = new SqlCommand(strSQL, oConn);
            oComd.CommandType = CommandType.Text;

            try
            {
                ret = oComd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                throw ex;
            }

            oConn.Close();
            return ret;
        }
    }
}
